﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Common.Utilities;
using Common.Utilities.Dispatcher;
using Common.Utilities.OpenXml;

namespace Workflows.Components.Reports.OpenXmlUtil
{
	/// <summary>
	/// 
	/// </summary>
	public partial class Excel2007ReportEditor : Form, IComponentEditor
	{
		/// <summary>
		/// 
		/// </summary>
		public Excel2007ReportEditor()
		{
			InitializeComponent();
		}

		private void Excel2007ReportEditor_Load(object sender, EventArgs e)
		{
			this.cbo_ChartType.Items.Clear();
			for(int i=0;i<=2;i++)
			{
				ExcelChartType chartType = (ExcelChartType) Enum.ToObject(typeof (ExcelChartType), i);
				this.cbo_ChartType.Items.Add(Enum.GetName(typeof (ExcelChartType), chartType));
			}
		}

		#region Implementation of IComponentEditor
		private object _ActivityNode;
		private Excel2007ReportComponent _ReportComponent;
		public event UpdateComponentSettings UpdateComponentSettingEvent;

		/// <summary></summary>
		public object OwnerActivityNode
		{
			get { return _ActivityNode;}
			set { _ActivityNode=value; }
		}

		/// <summary></summary>
		public Type WorkflowComponentType
		{
			get { return typeof (Excel2007ReportComponent); }
		}

		/// <summary></summary>
		public void LoadSettings(GlobalVariable[] globalVars, WorkflowComponentBase[] fromComponents, object activityNode, WorkflowComponentBase wfComponent)
		{
			this._ActivityNode = activityNode;
			this._ReportComponent = (Excel2007ReportComponent) wfComponent;

			Excel2007ReportInstruction instruction = (Excel2007ReportInstruction) this._ReportComponent.Instruction;
			this.txt_TemplateFile.Text = instruction.ExcelTemplateFilePath;
			this.txt_OutputFilePath.Text = instruction.ExcelReportFilePath;
			this.DisplayDataProviders(instruction.TableDataProviders);
			this.DisplayBindings(instruction.WorksheetDataBindings);
			this.PopulateChartTables();
			this.PopulateChartDefinitions();
		}

		/// <summary></summary>
		public void SaveSettings()
		{
			Excel2007ReportInstruction instruction = (Excel2007ReportInstruction) this._ReportComponent.Instruction;
			instruction.ExcelReportFilePath = this.txt_OutputFilePath.Text;
			instruction.ExcelTemplateFilePath = this.txt_TemplateFile.Text;
			instruction.TableDataProviders = this.GetDataProviders();
			instruction.WorksheetDataBindings = this.GetBindings();

			this._ReportComponent.Instruction = instruction;
		}

		/// <summary></summary>
		public TestResult Test()
		{
			throw new System.NotImplementedException();
		}

		#endregion

		private void btn_Browse_Click(object sender, EventArgs e)
		{
			OpenFileDialog dlg=new OpenFileDialog();
			dlg.Filter = "Excel files(*.xlsx)|*.xlsx|All files(*.*)|*.*";
			if(dlg.ShowDialog()==DialogResult.OK)
				this.txt_TemplateFile.Text = dlg.FileName;
		}

		private void btn_Save_Click(object sender, EventArgs e)
		{
			SaveFileDialog dlg=new SaveFileDialog();
			dlg.Filter = "Excel files(*.xlsx)|*.xlsx|All files(*.*)|*.*";
			if (dlg.ShowDialog() == DialogResult.OK)
				this.txt_OutputFilePath.Text = dlg.FileName;
		}

		#region providers
		private void btn_AddDataProvider_Click(object sender, EventArgs e)
		{
			DataGridViewRow dr = new DataGridViewRow();
			DataGridViewTextBoxCell cellConn=new DataGridViewTextBoxCell();
			cellConn.Value = this.txt_ConnStr.Text;
			dr.Cells.Add(cellConn);
			DataGridViewTextBoxCell cellSql=new DataGridViewTextBoxCell();
			cellSql.Value = this.rt_Sql.Text;
			dr.Cells.Add(cellSql);
			DataGridViewTextBoxCell cellTbls = new DataGridViewTextBoxCell();
			cellTbls.Value = this.txt_OutputTableNames.Text;
			dr.Cells.Add(cellTbls);
			DataGridViewTextBoxCell cellParams=new DataGridViewTextBoxCell();
			cellParams.Value = this.txt_Parameters.Text;
			dr.Cells.Add(cellParams);
			DataGridViewCheckBoxCell cellSp=new DataGridViewCheckBoxCell();
			cellSp.Value = this.ck_StoredProc.Checked;
			dr.Cells.Add(cellSp);
			this.dv_Providers.Rows.Add(dr);

			this.PopulateChartTables();
		}

		private List<TableDataFromSql> GetDataProviders()
		{
			List<TableDataFromSql> providers=new List<TableDataFromSql>();
			
			foreach(DataGridViewRow dr in this.dv_Providers.Rows)
			{
				if(!dr.IsNewRow)
				{
					string connstr = (string) dr.Cells[0].Value;
					string sql = (string) dr.Cells[1].Value;
					string tblNames = (string) dr.Cells[2].Value;
					string parameters = (string) dr.Cells[3].Value;
					bool isSP = (bool) dr.Cells[4].Value;
					TableDataFromSql provider=new TableDataFromSql();
					provider.ConnStr = connstr;
					provider.SelectSql = sql;
					provider.OutputTableNames =StringUtil.ParseToList(tblNames);
					provider.Parameters = StringUtil.ParseToDictionary(parameters);
					provider.IsStoredProc = isSP;
					providers.Add(provider);
				}
			}
			return providers;
		}

		private void DisplayDataProviders(List<TableDataFromSql> providers)
		{
			this.dv_Providers.Rows.Clear();
			if(providers !=null && providers.Count>0)
			{
				foreach(TableDataFromSql provider in providers)
				{
					DataGridViewRow dr = new DataGridViewRow();
					DataGridViewTextBoxCell cellConn = new DataGridViewTextBoxCell();
					cellConn.Value = provider.ConnStr;
					dr.Cells.Add(cellConn);
					DataGridViewTextBoxCell cellSql = new DataGridViewTextBoxCell();
					cellSql.Value = provider.SelectSql;
					dr.Cells.Add(cellSql);
					DataGridViewTextBoxCell cellTbls = new DataGridViewTextBoxCell();
					cellTbls.Value = StringUtil.ToString(provider.OutputTableNames);
					dr.Cells.Add(cellTbls);
					DataGridViewTextBoxCell cellParams = new DataGridViewTextBoxCell();
					cellParams.Value =StringUtil.ToString(provider.Parameters);
					dr.Cells.Add(cellParams);
					DataGridViewCheckBoxCell cellSp = new DataGridViewCheckBoxCell();
					cellSp.Value = provider.IsStoredProc;
					dr.Cells.Add(cellSp);
					this.dv_Providers.Rows.Add(dr);
				}
			}
		}
		#endregion

		#region bindings
		private void btn_AddWorksheetBinding_Click(object sender, EventArgs e)
		{
			if(string.IsNullOrEmpty(this.txt_SheetName.Text))
			{
				MessageBox.Show("Enter worksheet name");
				this.txt_SheetName.Focus();
				return;
			}
			if(string.IsNullOrEmpty(this.txt_TableNames.Text))
			{
				MessageBox.Show("Enter table name(s)");
				this.txt_TableNames.Focus();
				return;
			}

			DataGridViewRow dr = new DataGridViewRow();
			DataGridViewTextBoxCell cellSheet = new DataGridViewTextBoxCell();
			cellSheet.Value = this.txt_SheetName.Text;
			dr.Cells.Add(cellSheet);
			DataGridViewTextBoxCell cellHeaderPos = new DataGridViewTextBoxCell();
			cellHeaderPos.Value = (int) this.num_HeaderPos.Value;
			dr.Cells.Add(cellHeaderPos);
			DataGridViewTextBoxCell cellKeyPos = new DataGridViewTextBoxCell();
			cellKeyPos.Value = (int) this.num_KeyPos.Value;
			dr.Cells.Add(cellKeyPos);
			DataGridViewTextBoxCell cellTbls = new DataGridViewTextBoxCell();
			cellTbls.Value = this.txt_TableNames.Text;
			dr.Cells.Add(cellTbls);
			DataGridViewTextBoxCell cellKeyFields = new DataGridViewTextBoxCell();
			cellKeyFields.Value = this.txt_KeyFields.Text;
			dr.Cells.Add(cellKeyFields);
			DataGridViewTextBoxCell cellColumns = new DataGridViewTextBoxCell();
			if (!string.IsNullOrEmpty(this.txt_ColumnOrder.Text))
				cellColumns.Value = this.txt_ColumnOrder.Text;
			else
			{
				string[] tblNames = StringUtil.Split(this.txt_TableNames.Text);
				Dictionary<string,TableColumn> cols=new Dictionary<string, TableColumn>();
				List<TableDataFromSql> providers = this.GetDataProviders();
				foreach (string tblName in tblNames)
				{
					foreach (TableDataFromSql provider in providers)
					{
						if(provider.OutputTableNames.Contains(tblName))
						{
							if (provider.IsStoredProc)
							{
								try
								{
									TableColumn[] tblCols = DbUtil.GetTableColumnsFromStoredProc(
										DataProviderType.MSSQL, provider.ConnStr, provider.SelectSql);
									foreach (TableColumn col in tblCols)
									{
										if (!cols.ContainsKey(col.ColumnName))
										{
											cols.Add(col.ColumnName, col);
										}
									}
								}
								catch (Exception)
								{
								}
							}
							else
							{
								try
								{
									TableColumn[] tblCols = DbUtil.GetTableColumns(
										DataProviderType.MSSQL, provider.ConnStr, provider.SelectSql);
									foreach (TableColumn col in tblCols)
									{
										if (!cols.ContainsKey(col.ColumnName))
										{
											cols.Add(col.ColumnName, col);
										}
									}

								}
								catch (Exception)
								{
								}
							}
							break;
						}
					}
				}

				if(cols.Count>0)
				{
					StringBuilder buffer=new StringBuilder();
					int colPos = 0;
					foreach(string colName in cols.Keys)
					{
						if (buffer.Length > 0)
							buffer.Append(",");
						colPos++;
						buffer.Append(colName + "=" + colPos);
					}
					cellColumns.Value = buffer.ToString();
				}
			}
			dr.Cells.Add(cellColumns);
			this.dv_Binding.Rows.Add(dr);
		}

		private Dictionary<string,WorksheetTableBinding> GetBindings()
		{
			Dictionary<string,WorksheetTableBinding> bindings=new Dictionary<string, WorksheetTableBinding>();
			foreach(DataGridViewRow dr in this.dv_Binding.Rows)
			{
				if(!dr.IsNewRow)
				{
					string sheetName = (string) dr.Cells[0].Value;
					WorksheetTableBinding binding=new WorksheetTableBinding();
					binding.ColumnHeaderRowPosition = (int)dr.Cells[1].Value;
					binding.KeyColumnPosition = (int)dr.Cells[2].Value;
					binding.TableNames = StringUtil.ParseToList((string) dr.Cells[3].Value);
					binding.TableKeyFields = StringUtil.ParseToDictionary((string) dr.Cells[4].Value);
					Dictionary<string, string> colPositions = StringUtil.ParseToDictionary((string)dr.Cells[5].Value);
					binding.ColumnPositions=new Dictionary<string, int>();
					foreach(string colName in colPositions.Keys)
					{
						binding.ColumnPositions.Add(colName, int.Parse(colPositions[colName]));
					}
					bindings.Add(sheetName,binding);
				}
			}
			return bindings;
		}

		private void DisplayBindings(Dictionary<string,WorksheetTableBinding> bindings)
		{
			this.dv_Binding.Rows.Clear();
			foreach(string sheetName in bindings.Keys)
			{
				WorksheetTableBinding binding = bindings[sheetName];
				DataGridViewRow dr = new DataGridViewRow();
				DataGridViewTextBoxCell cellSheet = new DataGridViewTextBoxCell();
				cellSheet.Value = sheetName;
				dr.Cells.Add(cellSheet);
				DataGridViewTextBoxCell cellHeaderPos = new DataGridViewTextBoxCell();
				cellHeaderPos.Value = binding.ColumnHeaderRowPosition;
				dr.Cells.Add(cellHeaderPos);
				DataGridViewTextBoxCell cellKeyPos = new DataGridViewTextBoxCell();
				cellKeyPos.Value = binding.KeyColumnPosition;
				dr.Cells.Add(cellKeyPos);
				DataGridViewTextBoxCell cellTbls = new DataGridViewTextBoxCell();
				cellTbls.Value = StringUtil.ToString(binding.TableNames);
				dr.Cells.Add(cellTbls);
				DataGridViewTextBoxCell cellKeyFields = new DataGridViewTextBoxCell();
				cellKeyFields.Value = StringUtil.ToString(binding.TableKeyFields);
				dr.Cells.Add(cellKeyFields);
				DataGridViewTextBoxCell cellColumns = new DataGridViewTextBoxCell();
				Dictionary<string,string> colPositions=new Dictionary<string, string>();
				foreach(string colName in binding.ColumnPositions.Keys)
				{
					colPositions.Add(colName, binding.ColumnPositions[colName].ToString());
				}
				cellColumns.Value = StringUtil.ToString(colPositions);
				dr.Cells.Add(cellColumns);
				this.dv_Binding.Rows.Add(dr);
			}
		}
		#endregion

		#region charts 
		private void PopulateChartDefinitions()
		{
			this.cbo_AllCharts.Items.Clear();
			Excel2007ReportInstruction instruction = (Excel2007ReportInstruction) this._ReportComponent.Instruction;
			if(instruction.Charts != null && instruction.Charts.Count>0)
			{
				foreach(string chartName in instruction.Charts.Keys)
				{
					this.cbo_AllCharts.Items.Add(chartName);
				}

			    this.cbo_AllCharts.SelectedIndex = 0;
			}
		}

		private void btn_AddChartDef_Click(object sender, EventArgs e)
		{
			Excel2007ReportInstruction instruction = (Excel2007ReportInstruction)this._ReportComponent.Instruction;
			string chartName = this.txt_ChartName.Text;
			if(instruction.Charts !=null && instruction.Charts.ContainsKey(chartName))
			{
				MessageBox.Show("Please use distinct name for chart");
				this.txt_ChartName.Focus();
				return;
			}

			ChartDefinition chartDef=new ChartDefinition();
			chartDef.ChartName = chartName;
			chartDef.SheetName = this.txt_ChartSheetName.Text;
			chartDef.Title = this.txt_ChartTitle.Text;
			chartDef.ChartType = (ExcelChartType) Enum.ToObject(typeof (ExcelChartType), this.cbo_ChartType.SelectedIndex);
		    chartDef.ApplyStyle = this.ck_ChartStyle.Checked;
			chartDef.ChartDataTable = this.cbo_ChartTable.SelectedIndex >= 0
			                          	? this.cbo_ChartTable.SelectedItem.ToString()
			                          	: string.Empty;
			chartDef.CategoryColumnName = this.cbo_CategoryColumn.SelectedIndex >= 0
			                              	? this.cbo_CategoryColumn.SelectedItem.ToString()
			                              	: string.Empty;
			chartDef.CategoryFormular = this.txt_CategoryFormular.Text;
			chartDef.SeriesColumns=new List<string>();
			for(int i=0;i<this.clb_SeriesColumns.Items.Count;i++)
			{
				if(this.clb_SeriesColumns.GetItemChecked(i))
					chartDef.SeriesColumns.Add(this.clb_SeriesColumns.Items[i].ToString());
			}
			chartDef.SeriesDataFormulars=new Dictionary<string, string>();
			if(!string.IsNullOrEmpty(this.rt_SeriesFormular.Text))
			{
				string[] seriesFs = StringUtil.Split(this.rt_SeriesFormular.Text);
				foreach(string seriesF in seriesFs)
				{
					if(seriesF.IndexOf("=")>0)
					{
						string lbl = seriesF.Substring(0, seriesF.IndexOf("="));
						string formular = seriesF.Substring(seriesF.IndexOf("=") + 1);
						chartDef.SeriesDataFormulars.Add(lbl,formular);
					}
				}
			}
			if(instruction.Charts==null)
				instruction.Charts=new Dictionary<string, ChartDefinition>();
			instruction.Charts.Add(chartName,chartDef);
			this.cbo_AllCharts.Items.Add(chartName);
		}

        private void btn_SaveChartDef_Click(object sender, EventArgs e)
        {
            Excel2007ReportInstruction instruction = (Excel2007ReportInstruction)this._ReportComponent.Instruction;
            string chartName = this.txt_ChartName.Text;
            ChartDefinition chartDef = new ChartDefinition();
            chartDef.ChartName = chartName;
            chartDef.SheetName = this.txt_ChartSheetName.Text;
            chartDef.Title = this.txt_ChartTitle.Text;
            chartDef.ChartType = (ExcelChartType)Enum.ToObject(typeof(ExcelChartType), this.cbo_ChartType.SelectedIndex);
            chartDef.ApplyStyle = this.ck_ChartStyle.Checked;
            chartDef.ChartDataTable = this.cbo_ChartTable.SelectedIndex >= 0
                                        ? this.cbo_ChartTable.SelectedItem.ToString()
                                        : string.Empty;
            chartDef.CategoryColumnName = this.cbo_CategoryColumn.SelectedIndex >= 0
                                            ? this.cbo_CategoryColumn.SelectedItem.ToString()
                                            : string.Empty;
            chartDef.CategoryFormular = this.txt_CategoryFormular.Text;
            chartDef.SeriesColumns = new List<string>();
            for (int i = 0; i < this.clb_SeriesColumns.Items.Count; i++)
            {
                if (this.clb_SeriesColumns.GetItemChecked(i))
                    chartDef.SeriesColumns.Add(this.clb_SeriesColumns.Items[i].ToString());
            }
            chartDef.SeriesDataFormulars = new Dictionary<string, string>();
            if (!string.IsNullOrEmpty(this.rt_SeriesFormular.Text))
            {
                string[] seriesFs = StringUtil.Split(this.rt_SeriesFormular.Text);
                foreach (string seriesF in seriesFs)
                {
                    if (seriesF.IndexOf("=") > 0)
                    {
                        string lbl = seriesF.Substring(0, seriesF.IndexOf("="));
                        string formular = seriesF.Substring(seriesF.IndexOf("=") + 1);
                        chartDef.SeriesDataFormulars.Add(lbl, formular);
                    }
                }
            }
            if (instruction.Charts == null)
                instruction.Charts = new Dictionary<string, ChartDefinition>();
            if (instruction.Charts.ContainsKey(chartName))
                instruction.Charts[chartName] = chartDef;
            else
            {
                instruction.Charts.Add(chartName, chartDef);
                this.cbo_AllCharts.Items.Add(chartName);
            }
        }

		private void btn_RemoveChartDef_Click(object sender, EventArgs e)
		{
			if(this.cbo_AllCharts.SelectedIndex>=0)
			{
				Excel2007ReportInstruction instruction = (Excel2007ReportInstruction) this._ReportComponent.Instruction;
				string chartName = this.cbo_AllCharts.SelectedItem.ToString();
				instruction.Charts.Remove(chartName);
				this.cbo_AllCharts.Items.RemoveAt(this.cbo_AllCharts.SelectedIndex);
				this.cbo_AllCharts.SelectedIndex = -1;
				this.cbo_AllCharts.Text = string.Empty;
			}
		}

		private void cbo_ChartTable_SelectedIndexChanged(object sender, EventArgs e)
		{
			if (this.cbo_ChartTable.SelectedIndex >= 0)
			{
				TableColumn[] cols = null;
				string tblname = this.cbo_ChartTable.SelectedItem.ToString();
				List<TableDataFromSql> providers = this.GetDataProviders();
				foreach (TableDataFromSql provider in providers)
				{
					if (provider.OutputTableNames.Contains(tblname))
					{
						if (provider.IsStoredProc)
						{
							try
							{
								cols = DbUtil.GetTableColumnsFromStoredProc(DataProviderType.MSSQL, provider.ConnStr, provider.SelectSql);
							}
							catch (Exception)
							{
							}

						}
						else
						{
							try
							{
								cols = DbUtil.GetTableColumns(DataProviderType.MSSQL, provider.ConnStr, provider.SelectSql);
							}
							catch (Exception)
							{
							}
						}
						break;
					}
				}

				this.cbo_CategoryColumn.Items.Clear();
				this.clb_SeriesColumns.Items.Clear();
				if (cols != null && cols.Length > 0)
				{
					foreach (TableColumn col in cols)
					{
						this.cbo_CategoryColumn.Items.Add(col.ColumnName);
						this.clb_SeriesColumns.Items.Add(col.ColumnName);
					}
				}
			}
		}

		private void cbo_AllCharts_SelectedIndexChanged(object sender, EventArgs e)
		{
			if(this.cbo_AllCharts.SelectedIndex>=0)
			{
				string chartName = this.cbo_AllCharts.SelectedItem.ToString();
				Excel2007ReportInstruction instruction = (Excel2007ReportInstruction)this._ReportComponent.Instruction;
				ChartDefinition chartDef = instruction.Charts[chartName];
				this.txt_ChartName.Text = chartDef.ChartName;
				this.txt_ChartSheetName.Text = chartDef.SheetName;
				this.txt_ChartTitle.Text = chartDef.Title;
				this.cbo_ChartType.SelectedIndex = (int) chartDef.ChartType;
			    this.ck_ChartStyle.Checked = chartDef.ApplyStyle;
				if(!string.IsNullOrEmpty(chartDef.ChartDataTable))
				{
					for(int i=0;i<this.cbo_ChartTable.Items.Count;i++)
					{
						if(this.cbo_ChartTable.Items[i].ToString()==chartDef.ChartDataTable)
						{
							this.cbo_ChartTable.SelectedIndex = i;
							break;
						}
					}
				}
				else
				{
					this.cbo_ChartTable.SelectedIndex = -1;
					this.cbo_ChartTable.Text = string.Empty;
				}
				if(!string.IsNullOrEmpty(chartDef.CategoryColumnName))
				{
					for(int i=0;i<this.cbo_CategoryColumn.Items.Count;i++)
					{
						if(this.cbo_CategoryColumn.Items[i].ToString()==chartDef.CategoryColumnName)
						{
							this.cbo_CategoryColumn.SelectedIndex = i;
							break;
						}
					}
				}
				else
				{
					this.cbo_CategoryColumn.SelectedIndex = -1;
					this.cbo_CategoryColumn.Text = string.Empty;
				}
				if(chartDef.SeriesColumns!=null && chartDef.SeriesColumns.Count>0)
				{
					for(int i=0;i<this.clb_SeriesColumns.Items.Count;i++)
					{
						if(chartDef.SeriesColumns.Contains(this.clb_SeriesColumns.Items[i].ToString()))
						{
							this.clb_SeriesColumns.SetItemChecked(i,true);
						}
						else
						{
							this.clb_SeriesColumns.SetItemChecked(i,false);
						}
					}
				}
				
				if(this.cbo_ChartTable.SelectedIndex>=0)
				{
					string tblName = this.cbo_ChartTable.SelectedItem.ToString();
					if(this.cbo_CategoryColumn.SelectedIndex>=0)
					{
						this.txt_CategoryFormular.Text = this.DetermineWorksheetRangeByDataBinding(
							tblName, this.cbo_CategoryColumn.SelectedItem.ToString());
					}
					Dictionary<string, string> formulars = new Dictionary<string, string>();
					for (int i = 0; i < this.clb_SeriesColumns.Items.Count; i++)
					{
						if (this.clb_SeriesColumns.GetItemChecked(i))
						{
							string colName = this.clb_SeriesColumns.Items[i].ToString();
							string formular = this.DetermineWorksheetRangeByDataBinding(tblName, colName);
							formulars.Add(colName, formular);
						}
					}

					StringBuilder buffer = new StringBuilder();
					foreach (string colName in formulars.Keys)
					{
						if (buffer.Length > 0)
						{
							buffer.Append(",");
						}
						buffer.Append(colName + "=" + formulars[colName]);
					}

					this.rt_SeriesFormular.Text = buffer.ToString();
				}
			}
		}

		private void clb_SeriesColumns_Click(object sender, EventArgs e)
		{
			if (this.cbo_ChartTable.SelectedIndex >= 0)
			{
				// column name -> formular 
				Dictionary<string, string> formulars = new Dictionary<string, string>();
				string tblName = this.cbo_ChartTable.SelectedItem.ToString();
				for (int i = 0; i < this.clb_SeriesColumns.Items.Count; i++)
				{
					if (this.clb_SeriesColumns.GetItemChecked(i))
					{
						string colName = this.clb_SeriesColumns.Items[i].ToString();
						string formular = this.DetermineWorksheetRangeByDataBinding(tblName, colName);
						formulars.Add(colName, formular);
					}
				}

				StringBuilder buffer = new StringBuilder();
				foreach (string colName in formulars.Keys)
				{
					if (buffer.Length > 0)
					{
						buffer.Append(",");
					}
					buffer.Append(colName + "=" + formulars[colName]);
				}

				this.rt_SeriesFormular.Text = buffer.ToString();
			}
			
		}

		private void PopulateChartTables()
		{
			List<TableDataFromSql> providers = this.GetDataProviders();
			this.cbo_ChartTable.Items.Clear();
			if(providers !=null && providers.Count>0)
			{
				foreach(TableDataFromSql provider in providers)
				{
					foreach (string newTblname in provider.OutputTableNames)
					{
						bool found = false;
						for (int i = 0; i < this.cbo_ChartTable.Items.Count; i++)
						{
							if (this.cbo_ChartTable.Items[i].ToString() == newTblname)
							{
								found = true;
								break;
							}
						}
						if (!found)
						{
							this.cbo_ChartTable.Items.Add(newTblname);
						}
					}
				}
			}
		}

		private void cbo_CategoryColumn_SelectedIndexChanged(object sender, EventArgs e)
		{
			if(this.cbo_ChartTable.SelectedIndex>=0 && this.cbo_CategoryColumn.SelectedIndex>=0)
			{
				this.txt_CategoryFormular.Text = this.DetermineWorksheetRangeByDataBinding(
					this.cbo_ChartTable.SelectedItem.ToString(),
					this.cbo_CategoryColumn.SelectedItem.ToString());
			}
		}

		/// <summary>
		/// if table1 (100 records) -> sheet1, column1 -> a then formular is: sheet1!$A$1:$A:101
		/// and 
		/// </summary>
		/// <param name="tableName"></param>
		/// <param name="columnName"></param>
		/// <returns></returns>
		private string DetermineWorksheetRangeByDataBinding(string tableName, string columnName)
		{
			List<TableDataFromSql> providers = this.GetDataProviders();
			Dictionary<string, WorksheetTableBinding> bindings = this.GetBindings();
			string worksheetName = string.Empty;
			string columnPos = string.Empty;
			int headerRosPos = 0;
			int tableRecordCount = 0;
			foreach(TableDataFromSql provider in providers)
			{
				if(provider.OutputTableNames.Contains(tableName))
				{
					if (provider.IsStoredProc)
						tableRecordCount = DbUtil.GetTableRecordCountUsingSP(DataProviderType.MSSQL, provider.ConnStr, provider.SelectSql);
					else
						tableRecordCount = DbUtil.GetTableRecordCount(DataProviderType.MSSQL, provider.ConnStr, provider.SelectSql);
					break;
				}
			}
			foreach(string sheetName in bindings.Keys)
			{
				WorksheetTableBinding binding = bindings[sheetName];
				if(binding.TableNames.Contains(tableName))
				{
					worksheetName = sheetName;
					foreach(string colName in binding.ColumnPositions.Keys)
					{
						if(colName==columnName)
						{
							columnPos = WorksheetHelper.GetColumnName(binding.ColumnPositions[colName]);
							headerRosPos = binding.ColumnHeaderRowPosition;
							break;
						}
					}
					if (!string.IsNullOrEmpty(columnPos))
						break;
				}
			}

			string formular = string.Format(
				"{0}!${1}${2}:${1}${3}", worksheetName, columnPos, headerRosPos + 1, headerRosPos + tableRecordCount);
			return formular;
		}
		#endregion

		private void btn_OK_Click(object sender, EventArgs e)
		{
			this.SaveSettings();
			if (this.UpdateComponentSettingEvent != null)
				this.UpdateComponentSettingEvent(this._ActivityNode, this._ReportComponent);

			this.Close();
		}

		private void btn_Cancel_Click(object sender, EventArgs e)
		{
			this.Close();
		}

	}
}
